﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Linq;

namespace MigrationTool
{
    /// <summary>
    /// 类模型对象
    /// </summary>
    public class ClassModel
    {
        /// <summary>
        /// 创建者
        /// </summary>
        public string Creator
        {
            get;
            set;
        }
        /// <summary>
        /// 创建日期
        /// </summary>
        public DateTime? CreateDate
        {
            get;
            set;
        }
        /// <summary>
        /// 类描述
        /// </summary>
        public string Description
        {
            get;
            set;
        }
        //public bool IsTarget
        //{
        //    get;
        //    set;
        //}
        //public IList<string> TemplateNames
        //{
        //    get;
        //    set;
        //}

        /// <summary>
        /// 字段集合
        /// </summary>
        public IList<FieldModel> Fields
        {
            get;
            set;
        }

        /// <summary>
        /// 版本
        /// </summary>
        public string Version
        {
            get;
            set;
        }
        /// <summary>
        /// 类名
        /// </summary>
        public string Name
        {
            get;
            set;
        }
    }



    public class RowModel
    {
        private IList<FieldModel> _fields = new List<FieldModel>();


        //private IDictionary<string, FieldModel> _identityField = new Dictionary<string, FieldModel>();
        private string _tablename;

        /// <summary>
        /// 自增列名,有大小写
        /// </summary>
        private string _identityname;
        /// <summary>
        /// 实际表和excel列的交集的在实际表(_fields)中的索引位置集合
        /// 如果实际表中找不到,则相应位置为-1
        /// </summary>
        private int[] incols = null;
        /// <summary>
        /// 实际表中但不在excel列中的实际表中的索引位置集合
        /// </summary>
        //private int[] excols = null;
        public RowModel(string tablename,SqlConnection con, List<FieldModel> fields)
        {
            List<int> defvalueColumns = new List<int>();
            StringBuilder sb = new StringBuilder();
            _fields = fields;
            this._tablename = tablename;
            for(int i=0;i<_fields.Count;i++)
            {
                //if (!fm.IsIdentity)
                //{
                var fm=_fields[i];
                if (fm.IsIdentity)
                    _identityname = fm.Name;
                //把所有表达式计算一遍(除了是guid的,日期类型如果也统一计算,问题应该不大)
                if (fm.DefaultSql != null && fm.Type != "uniqueidentifier")
                {
                    sb.Append(fm.DefaultSql);
                    sb.Append(",");
                    defvalueColumns.Add(i);
                }
                //if(fm.de)
                //((\d+(.\d*)?)),(('.*')),(('yyyy-MM-dd hh:mm:ss sss'))
                //}else
                //    _identityField[fm.Name.ToUpper()] = fm;
            }
            if (defvalueColumns.Count>0)
            {
                //sb = sb.Remove(sb.Length-1,1);
                SqlCommand command = con.CreateCommand();
                command.CommandText = string.Format("select {0}", sb.Remove(sb.Length - 1, 1));
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();
                    for (int iii = 0; iii < defvalueColumns.Count; iii++)
                    {
                        _fields[defvalueColumns[iii]].DefValue = reader[iii];
                    }
                }
            }
            //this._fields = fields;

        }

        /// <summary>
        /// excel列与数据库表列匹配检测,
        /// 返回在excel中有但数据库中没有的列
        /// </summary>
        /// <param name="exceldata"></param>
        /// <returns></returns>
        public void SetExcelData(DataTable exceldata)
        {
            StringBuilder sb = new StringBuilder();
            List<int> intLst = new List<int>();
            bool flg = false;
            for (int i = 0; i < exceldata.Columns.Count; i++)//查找excel列在实际表中的索引位置
            {
                flg = false;
                for (int j = 0; j < _fields.Count; j++)
                {
                    if (_fields[j].Name.ToUpper() == exceldata.Columns[i].ColumnName.ToUpper())
                    {
                        intLst.Add(j);
                        flg = true;
                        break;
                    }
                }
                if (!flg)
                {
                    //intLst.Add(-1);
                    sb.Append(exceldata.Columns[i].ColumnName + ":");

                }

            }
            if (sb.Length > 0)//excel中含有数据库表中没有的列,插入不会成功,直接返回
                throw new Exception(string.Format("导入数据表{0}中含有数据库表中不存在的列(列名不区分大小写){1}!",_tablename,sb.ToString()));

            //List<int> excLst = new List<int>();
            for (int i = 0; i < _fields.Count; i++)
            {
                if (intLst.IndexOf(i) < 0)
                {
                    if (!_fields[i].Nullable && !_fields[i].IsIdentity && _fields[i].DefaultSql == null)
                    {//在数据库表中有但Excel中没有,且不允许为null,且没有默认值,则往Excel中添加此列
                        exceldata.Columns.Add(new DataColumn()
                        {
                            ColumnName = _fields[i].Name,
                            DataType = ToType(_fields[i].Type)
                        });
                        intLst.Add(i);
                    }//else
                        //sb.Append(_fields[i].Name + ";");
                    //excLst.Add(i);
                }
                    
            }
            //if(sb.Length > 0)
            //    throw new Exception(string.Format("导入数据表{0}中不包含数据库表中必须输入且没有默认值的列(列名不区分大小写){1}!",_tablename,sb.ToString()));
            this.incols = intLst.ToArray();
            //this.excols = excLst.ToArray();
            //return sb.ToString();

        }

        private Type ToType(string dbtype)
        {
            switch (dbtype)
            {
                case "nvarchar":
                case "varchar":
                case "char":
                case "nchar":
                case "ntext":
                case "text":
                    return typeof(string);
                case "uniqueidentifier":
                    return typeof(Guid);
                case "int":
                    return typeof(Int32);
                case "bigint":
                    return typeof(Int64);
                case "bit":
                    return typeof(bool);
                case "datetime":
                case "smalldatetime":
                    return typeof(DateTime);
                case "float":
                    return typeof(Double);
                case "decimal":
                case "numeric":
                case "smallmoney":
                    return typeof(Decimal);
                case "real":
                    return typeof(Single);
                case "smallint":
                    return typeof(Int16);
                case "tynyint":
                    return typeof(byte);
            }
            throw new NotImplementedException("Can't convert DB type '" + dbtype +"' to .Net Type");
        }
        /// <summary>
        /// 从要插入的数据中移除自增列,返回自增列名
        /// </summary>
        /// <param name="datatable"></param>
        /// <returns></returns>
        public string TryRemoveIdentityColumn(DataTable datatable)
        {
            //string rtn = string.Empty;
            if (_identityname != null)
                //foreach (var id in _identityField.Values)
                //{
                datatable.Columns.Remove(_identityname);
            // rtn = id.Name;
            //}
            return _identityname;
        }

        public FieldModel this[string fieldName]
        {
            get
            {
                return _fields.Where<FieldModel>(fm => fm.Name == fieldName).FirstOrDefault();
            }
        }

        /// <summary>
        /// 创建sqlcommand
        /// 要在TryValidateRow之后运行
        /// </summary>
        /// <param name="_con">sql连接</param>
        /// <returns></returns>
        public SqlCommand CreateInsertSqlCommand(SqlConnection _con)
        {
            SqlCommand c = new SqlCommand();
            //string s;
            ////if (MigrationToolLogic._cachedInsertSql.TryGetValue(_tablename, out s))
            ////{
            ////    c.CommandText = s;
            ////    //return c;
            ////    //return s;
            ////}
            ////else
            ////{
            //string insersql = "insert into {0}({1})values({2})";
            ////var values = _fields.Values;
            //string columns = string.Join<FieldModel>(",", _fields.Where<FieldModel>(fm => fm.Value != null));//如果Value== null,表明此列不在excel列中,则插入的时候应该忽略
            //StringBuilder parameters = new StringBuilder(150);
            //foreach (var k in _fields)
            //{
            //    if (k.Value != null)//如果Value== null,表明此列不在excel列中,则插入的时候应该忽略
            //    {
            //        if (k.Value.IsSql)//如果是直接的sql文本,则拼接
            //        {
            //            parameters.Append(k.Value.Value);
            //        }
            //        else//设定参数
            //        {
            //            parameters.Append("@");
            //            parameters.Append(k);

            //        }
            //        parameters.Append(",");
            //    }


            //}

            ////string parameters = "@" + string.Join<string>(",@", values);
            //s = string.Format(insersql, _tablename, columns, parameters.Remove(parameters.Length - 1, 1));
            ////MigrationToolLogic._cachedInsertSql[_tablename] = s;
            //c.CommandText = s;
            ////}
            //c.Connection = _con;
            //c.Parameters.Clear();
            //foreach (var _f in _fields)
            //{
            //    if (_f.Value == null)
            //        continue;
            //    if (!_f.Value.IsSql)
            //        c.Parameters.Add(new SqlParameter("@" + _f, _f.Value.Value));
            //}

            return c;
        }
        /// <summary>
        /// 如果没有出错则返回true
        /// 同时在合适的时候修改行中的值为合适的值
        /// </summary>
        /// <param name="dr">数据行</param>
        /// <param name="errorList">错误消息</param>
        /// <returns>true如果没有错误</returns>
        /// <exception >当表中没有列的定义时</exception>
        public bool TryValidateRow(DataRow dr, SqlConnection con, out List<string> errorList, out List<string> warninglist)
        {
            //if(ErrorList == null)
            errorList = new List<string>();
            warninglist = new List<string>();
            FieldModel vm = null;
            bool needComputeSql = false;
            //SqlBulkCopy bc = null;
            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                //if (incols[i] != -1)//不可能为-1,如果找不到,在前面应该已经终止了
                //{
                    vm = _fields[incols[i]];
                    try
                    {
                        dr[i] = vm.ValidateAndConvertValue(dr[i]);
                        //if (!vm.Value.IsSql)
                        //    dr[i] = vm.Value.Value;
                        needComputeSql = needComputeSql || (vm.DefaultSql != null && dr[i] == DBNull.Value);
                    }
                    catch (WarningException ex)
                    {
                        warninglist.Add(ex.Message);
                        //ignore = ex.Ignore;
                    }
                    catch (Exception ex)
                    {
                        errorList.Add(ex.Message);
                    }
                //}
                //return rtn;
            }
            if (needComputeSql && errorList.Count == 0)//没有错误且有Sql的情况下通过sql设置默认值的值
            {


                //用一条sql把所有需要取计算值的值通过sql服务器取出来
                StringBuilder sb = new StringBuilder(50);
                List<int> defcolumn = new List<int>();
                for (int ii = 0; ii < dr.Table.Columns.Count; ii++)
                {
                    var fm = _fields[incols[ii]];

                    if (fm.DefaultSql != null && dr[ii] == DBNull.Value)
                    {
                        sb.Append(fm.DefaultSql);
                        sb.Append(",");
                        defcolumn.Add(ii);

                    }
                }
                SqlCommand command = con.CreateCommand();
                command.CommandText = string.Format("select {0}", sb.Remove(sb.Length-1,1));
                using (var reader = command.ExecuteReader())
                {
                    reader.Read();
                    for (int iii = 0; iii < defcolumn.Count; iii++)
                    {
                        dr[defcolumn[iii]] = reader[iii];
                    }
                }

            }
            return errorList.Count == 0;
        }
    }

    public class WarningException:Exception
    {
        //private bool _ignore=false;
        //public WarningException(string mess)
        //    : base(mess)
        //{
            
        //}
        //public bool Ignore
        //{
        //    get
        //    {
        //        return this._ignore;
        //    }
        //}
        /// <summary>
        /// 警告异常
        /// </summary>
        /// <param name="mess">警告消息</param>
        /// <param name="ignore">忽略此字段?</param>
        public WarningException(string mess)
            : base(mess)
        {
            //this._ignore = ignore;
        }
    }

    //public class Val
    //{
    //    private bool _IsSqlValue;
    //    private object _Value;
    //    public Val(object val,bool isSql=false)
    //    {
    //        _IsSqlValue=isSql;
    //        _Value=val;

    //    }

    //    public bool IsSql
    //    {
    //        get{
    //            return _IsSqlValue;
    //        }
    //    }
    //    public object Value
    //    {
    //        get{
    //            if(_Value != null)
    //            return _Value;
    //            return DBNull.Value;
    //        }
    //    }

    //}

    /// <summary>
    /// 字段模型(数据库
    /// )
    /// </summary>
    public class FieldModel 
    {
        /// <summary>
        /// 默认值,如果有则不为null(如果字段的默认值为null,则为DBNull.Value)
        /// </summary>
        public object DefValue
        {
            get;
            set;
        }

        //public string DefSql
        //{
        //    get;
        //    set;
        //}
        //private Val value;

        //public Val Value
        //{
        //    get
        //    {
        //        return value;
        //    }
        //}

        /// <summary>
        /// 校验数据,并把数据放到Value中
        /// </summary>
        /// <param name="o"></param>
        /// <returns>返回转换过后的值</returns>
        public object
            ValidateAndConvertValue(object o)
        {
            if (o as string =="NULL" ||o as string == string.Empty   || o == null)
                o = DBNull.Value;
            if (o == DBNull.Value)
            {
                if (!Nullable)
                {
                    if (DefaultSql == null)//如果没有默认值,且非自增列
                    {
                        //if(IsIdentity)
                        //    return;//是自增列,则不用赋值
                        if ("uniqueidentifier" == Type)//如果为Guid,则给个新的guid
                        {
                            //value = new Val("NEWID()",true);
                            //value = new Val(Guid.NewGuid());
                            return Guid.NewGuid();
                        }
                        if ("datetime" == Type)//如果是时间类型,则自动赋值为当前之间
                        {
                            //value = new Val(MainForm.Now);
                            return MainForm.Now;
                        }
                        if ("date" == Type)//如果为日期类型,则自动赋值为今天
                        {
                            //value = new Val(MainForm.Today);
                            return MainForm.Today;
                        }

                        throw new Exception(string.Format("字段[{0}]不能为null.", Name));
                    }
                    else 
                    {
                        //value = new Val(DefaultValue,true);
                        if (DefValue == DBNull.Value)
                            throw new Exception(string.Format("字段[{0}]不能为null.但输入数据和字段默认值都为null", Name));
                        else if (DefValue == null)//有默认值但未计算
                            return DBNull.Value;
                        return DefValue;
                    }
                       
                    
                }
                    
                else//可为null
                {
                    //value = new Val(null);
                    if (DefValue != null)
                        return DefValue;
                    return DBNull.Value;
                }

            }
            //else if (IsIdentity) //如果不为空,但为自增列,则发出警告
            //{
            //    throw new WarningException(string.Format("字段[{0}]为自增列,自动忽略输入值:", Name,o));//忽略此列

            //}

            
            if ("nvarchar,".IndexOf(Type) >= 0)
            {
                string s = o.ToString();
                if (IntegerPart >0 && s.Length > IntegerPart)
                    throw new Exception(string.Format("字段[{0}]长度不能超过{1}:'{2}'", Name,IntegerPart,s));
                //return ;
               // value = new Val(o.ToString());
                return s;// null;
            }

            if(("int,").IndexOf(Type) >=0)
            {
                try
                {
                   // return ;
                    //value = new Val(Convert.ToInt32(o));;
                    return Convert.ToInt32(o);// null;
                }
                catch
                {
                    throw new Exception(string.Format("字段[{0}]不能转换为'{1}':{2}", Name, Type, o));
                }
            }
            if (("smallint,").IndexOf(Type) >= 0)
            {
                try
                {
                    //return Convert.ToInt16(o);
                    //value = new Val(Convert.ToInt16(o));
                    return Convert.ToInt16(o);// null;
                }
                catch
                {
                    throw new Exception(string.Format("字段[{0}]不能转换为'{1}':{2}", Name, Type, o));
                }
            }
            if (("bigint,").IndexOf(Type) >= 0)
            {
                try
                {
                    //value = new Val(Convert.ToInt64(o));
                    return Convert.ToInt64(o);// null;
                }
                catch
                {
                    throw new Exception(string.Format("字段[{0}]不能转换为'{1}':{2}", Name, Type, o));
                }
            }
            if ("decimal,".IndexOf(Type) >=0)
            {
                //string os = o.ToString();
                decimal od;
                try
                {
                    od = Convert.ToDecimal(o);
                }
                catch
                {

                    throw new Exception(string.Format("字段[{0}]不能转换为'{1}':{2}", Name, Type, o));
                }

                string os = o.ToString();
                string[] splits=os.Split('.');
                if (splits[0].Length > IntegerPart)
                    throw new Exception(string.Format("字段[{0}]整数部分长度不能超过{1}:'{2}'", Name, IntegerPart, o));
                if (splits.Length>1)
                {
                    //过滤掉最后的0
                    int i = splits[1].Length -1;
                    for (; i >= 0 && splits[1][i] == '0'; i--) ;
                        if (i+1 > FractionalPart)
                            throw new Exception(string.Format("字段[{0}]小数部分长度不能超过{1}:'{2}'", Name, IntegerPart, o));
                }
                //value= new Val(od);
                return od;
            }
            if ("datetime,date,".IndexOf(Type) >= 0)
            {
                try
                {
                    //value= new Val(Convert.ToDateTime(o));
                    return Convert.ToDateTime(o);
                }
                catch
                {
                    throw new Exception(string.Format("字段[{0}]不能转换为'{1}':{2}", Name, Type, o));
                }

            }
            if ("bit,".IndexOf(Type) >= 0)
            {
                //value = new Val(Convert.ToBoolean(o));
                return Convert.ToBoolean(o);
            }
            if ("uniqueidentifier,".IndexOf(Type) >= 0)
            {
                //value = new Val(Guid.Parse(o.ToString()));
                return Guid.Parse(o.ToString());
            }
            //value= new Val(o);
            return o;
        }

        /// <summary>
        /// 字段标签
        /// </summary>
        public string Label
        {
            get;
            set;
        }
        /// <summary>
        /// 字段名称
        /// </summary>
        public string Name
        {
            get;
            set;
        }
        /// <summary>
        /// 字段类型
        /// </summary>
        public string Type
        {
            get;
            set;
        }
        /// <summary>
        /// 可空?
        /// </summary>
        public bool Nullable
        {
            get;
            set;
        }
        /// <summary>
        /// 是否为唯一索引或者唯一索引中的一部分
        /// </summary>
        public bool IsUnique
        {
            get;
            set;
        }

        /// <summary>
        /// 索引所在的组,如果没有索引,则为999
        /// </summary>
        public short? KeyGroup
        {
            get;
            set;
        }
        /// <summary>
        /// 是否为自增类型的变量
        /// </summary>
        public bool IsIdentity
        {
            get;
            set;
        }
        /// <summary>
        /// 默认值的sql表达式
        /// 如果为null,则没有表达式
        /// </summary>
        public string DefaultSql
        {
            get;
            set;
        }
        ///// <summary>
        ///// json序列化对象字段?
        ///// </summary>
        //public bool DataMember
        //{
        //    get;
        //    set;
        //}
        ///// <summary>
        ///// 说明
        ///// </summary>
        //public string Memo
        //{
        //    get;
        //    set;
        //}

        /// <summary>
        /// 整数部分,如果为字符串,则为字符串的长度(-1表示MAX:2^31-1)
        /// </summary>
        public int IntegerPart { get; set; }

        /// <summary>
        /// 小数部分
        /// </summary>
        public int? FractionalPart { get; set; }

        ///// <summary>
        ///// 数值校验器
        ///// LowThanZero
        ///// GreateThanZero等
        ///// </summary>
        //public string DecimalValidator { get; set; }

        ///// <summary>
        ///// 字段需要校验判断
        ///// </summary>
        //public bool IsValidator
        //{
        //    get
        //    {
        //        return Type != "DateTime?" && (DecimalValidator != null || !Nullable);
        //    }
        //}
        public override string ToString()
        {
            return Name.ToUpper();
        }
    }
}
